<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Imports\DataExcelImport;
use App\Models\Category;
use App\Models\SubCategory;
use App\Models\DataExcel;
use App\Models\Sender;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Requests\UploadDataExcel;
use Illuminate\Support\Facades\Session;
use Illuminate\Support\Collection;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\DB;

class DataExcelController extends Controller
{
    public function uploadFile()
    {
        $categories = Category::all();
        $subcategories = SubCategory::all();
        return view('dashboard.excel.add', compact('categories', 'subcategories'));
    }

    public function downloadFileExcel(UploadDataExcel $request)
    {
        // زيادة حد الوقت والذاكرة للملفات الكبيرة
        set_time_limit(0);
        ini_set('memory_limit', '1024M');
        ini_set('max_execution_time', 0);

        if (!$request->hasFile('file')) {
            toastr()->error('Please upload at least one file.');
            return redirect()->back();
        }

        $files = $request->file('file');
        $invalidData = [];
        $invalidHeaders = [];
        $emailsSeen = [];
        
        if (empty($request->category_name) || empty($request->subcategory_name)) {
            toastr()->error('Category and Subcategory are required.');
            return redirect()->back();
        }
        
        $category = Category::findOrFail($request->category_name);
        $cat = Category::updateOrCreate(['name' => $category->name]);
        $subcategory = Subcategory::findOrFail($request->subcategory_name[0]);
        $subcat = Subcategory::updateOrCreate([
            'name' => $subcategory->name,
            'category_id' => $cat->id
        ]);

        try {
            foreach ($files as $file) {
                // Accept any Excel format
                $allowedExtensions = ['xlsx', 'xls', 'csv'];
                $fileExtension = strtolower($file->getClientOriginalExtension());
                if (!in_array($fileExtension, $allowedExtensions)) {
                    toastr()->error('Please upload only Excel files (.xlsx, .xls, .csv)');
                    return redirect()->back();
                }
                
                $fileName = time() . '-' . uniqid() . '.' . $fileExtension;
                $destinationPath = storage_path('Uploads/excel_files/');
                if (!file_exists($destinationPath)) {
                    mkdir($destinationPath, 0777, true);
                }
                $file->move($destinationPath, $fileName);
                $filePath = $destinationPath . $fileName;

                // قراءة الملف بطريقة محسنة للملفات الكبيرة
                try {
                    // استخدام chunk reading للملفات الكبيرة
                    $sheets = Excel::toArray(null, $filePath);
                } catch (\Exception $e) {
                    Log::warning('Primary Excel reading failed, trying alternative method: ' . $e->getMessage());
                    try {
                        $collectionSheets = Excel::toCollection(null, $filePath);
                        $sheets = [];
                        foreach ($collectionSheets as $sheetCol) {
                            $sheets[] = $sheetCol->toArray();
                        }
                    } catch (\Exception $e2) {
                        toastr()->error('Unable to read Excel file format. Please ensure it\'s a valid Excel file.');
                        continue;
                    }
                }

                if (empty($sheets) || count($sheets) < 1) {
                    toastr()->error('The uploaded Excel file is empty or has no data.');
                    return redirect()->back();
                }

                // header map
                $headerMap = [
                    'destination_name' => [
                        'اسم المدرسه', 'اسم المصنع', 'اسم الجهة', 'أسم الجهة', 'اسم الشركة', 'أسم المؤسسة',
                        'company', 'companyname', 'corporation', 'corporationname', 'اسم العميل', 'clientname',
                        'destinationname', 'اسم', 'الاسم', 'name','اسم الصيدليه','name and title','NAME','Company Name','اسم المدرس',
                        'client', 'organization', 'institution', 'school', 'factory', 'pharmacy','المكان','اسم المعرض','qBF1Pd','اسم المكان','اسم المدرس','name and title',
                    ],
                    'address' => [
                        'address', 'location', 'العنوان', 'عنوان', 'الموقع', 'المدينة', 'المنطقة',
                        'addressdetails', 'site', 'المكان', 'city', 'region', 'area','W4Efsd 3',
                    ],
                    'phone' => [
                        'phone', 'telephone', 'mobile', 'تليفون', 'موبايل', 'هاتف', 'جوال', 'رقم الهاتف',
                        'phonenumber', 'contact', 'رقم الجوال','Phone Number','الهاتف','الرقم', 'tel', 'cell','W4Efsd 6','W4Efsd 4','Tel/Fax'
                    ],
                    'phone2' => [
                        'phone2', 'telephone2', 'mobile2', 'تليفون2', 'موبايل2', 'هاتف2', 'جوال2',
                        'secondaryphone', 'رقم هاتف آخر', 'alternatephone', 'phone 2','W4Efsd 7'
                    ],
                    'email' => [
                        'email', 'emailaddress', 'الايميل', 'البريدالالكتروني', 'بريدالكتروني',
                        'electronicmail', 'ايميل', 'البريد','E-MAIL', 'mail', 'e mail', 'بريد الكتروني',
                        'البريد الالكتروني', 'الإيميل', 'إيميل','E-mail'
                    ],
                    'country' => [
                        'country', 'بلد', 'البلد', 'الدولة', 'دولة', 'nation'
                    ],
                    'website' => [
                        'website', 'الموقع الالكتروني', 'websiteurl', 'web', 'موقع الكتروني',
                        'الموقع الإلكتروني','ويب سايت','الويب سايت', 'url', 'site', 'موقع إلكتروني','lcr4fd href','Website/Email'
                    ],
                    'google_map' => [
                        'googlemap', 'map', 'googlemap', 'googlemap', 'الموقع', 'locationmap',
                        'خريطة', 'خريطةجوجل','جوجل ماب','Google Maps', 'maps', 'location','hfpxzc href',
                    ],
                    'responsible_name' => [
                        'name', 'fullname', 'اسم المسؤول', 'الموظف', 'صاحب الشركة', 'اسم الشخص', 'المسؤول',
                        'contact person', 'manager', 'owner'
                    ],
                    'firstname' => [
                        'firstname', 'الاسم الاول', 'اسم اول', 'الاسم', 'first', 'first name',
                    ],
                    'lastname' => [
                        'lastname', 'الاسم الأخير', 'الاسم الاخير', 'اسم اخير', 'familyname', 'اسم العائلة', 'last', 'last name'
                    ],
                ];

                // Normalize aliases once
                foreach ($headerMap as $key => $aliases) {
                    $headerMap[$key] = array_map(function($alias) { return $this->normalizeHeader($alias); }, $aliases);
                }
                
                $fileSeenRows = [];
                
                foreach ($sheets as $sheetIndex => $rows) {
                    if (empty($rows) || count($rows) < 1) continue;
                    
                    $originalHeaders = array_map(function ($header) {
                        return $this->sanitizeHeader($header);
                    }, $rows[0]);

                    $processedHeaders = array_map(function ($header) {
                        return $this->normalizeHeader($header);
                    }, $originalHeaders);
                    
                    $normalizedHeaders = [];
                    foreach ($headerMap as $key => $aliases) {
                        foreach ($processedHeaders as $index => $processedHeader) {
                            if (in_array($processedHeader, $aliases)) {
                                $normalizedHeaders[$key] = $index;
                                break;
                            }
                        }
                    }
                    
                    if (empty($normalizedHeaders)) {
                        $invalidHeaders[] = [
                            'file_name' => $fileName,
                            'sheet_index' => $sheetIndex,
                            'missing_headers' => 'No recognizable headers',
                            'found_headers' => implode(', ', $originalHeaders),
                        ];
                        continue;
                    }

                    // معالجة البيانات في chunks للملفات الكبيرة
                    $chunkSize = 500; // معالجة 500 صف في كل مرة
                    $totalRows = count($rows) - 1; // exclude header row
                    $processedRows = 0;
                    $batchData = []; // تجميع البيانات للإدراج المجمع
                    
                    for ($startIndex = 1; $startIndex < count($rows); $startIndex += $chunkSize) {
                        $endIndex = min($startIndex + $chunkSize, count($rows));
                        $currentChunk = array_slice($rows, $startIndex, $endIndex - $startIndex);
                        
                        foreach ($currentChunk as $i => $row) {
                            $actualRowIndex = $startIndex + $i;
                            
                            $row = array_map(function ($v) { return $this->sanitizeValue($v); }, $row);
                            $rowAssoc = [];
                            $firstName = null;
                            $lastName = null;
                            
                            foreach ($normalizedHeaders as $field => $index) {
                                $value = $row[$index] ?? null;
                                if ($field === 'firstname') { $firstName = trim($value); continue; }
                                else if ($field === 'lastname') { $lastName = trim($value); continue; }
                                else { $rowAssoc[$field] = $value; }
                            }
                            
                            if ($firstName || $lastName) {
                                $fullName = trim(($firstName ?? '') . ' ' . ($lastName ?? ''));
                                $rowAssoc['responsible_name'] = $fullName;
                            } else if (isset($rowAssoc['responsible_name'])) {
                                $rowAssoc['responsible_name'] = trim($rowAssoc['responsible_name']);
                            }
                            
                            if (!array_filter($rowAssoc)) continue;
                            
                            $expectedKeys = [
                                'destination_name','email','address','phone','phone2','country',
                                'website','google_map','responsible_name','category_name','subcategory_name'
                            ];
                            foreach ($expectedKeys as $k) {
                                if (!array_key_exists($k, $rowAssoc)) $rowAssoc[$k] = '';
                            }
                            
                            $rowAssoc['category_name'] = $cat->name;
                            $rowAssoc['subcategory_name'] = $subcat->name;
                            $rowAssoc['original_phone_raw'] = $rowAssoc['phone'] ?? '';
                            $rowAssoc['original_phone2_raw'] = $rowAssoc['phone2'] ?? '';
                            $rowAssoc['original_email_raw'] = $rowAssoc['email'] ?? '';
                            
                            $phonesFromPhone = $this->extractPhoneNumbersFromCell($rowAssoc['phone'] ?? '');
                            $phonesFromPhone2 = $this->extractPhoneNumbersFromCell($rowAssoc['phone2'] ?? '');
                            $phonesList = array_values(array_unique(array_filter(array_merge($phonesFromPhone, $phonesFromPhone2))));
                            $validPhones = array_filter($phonesList, function($p) { return $this->isValidPhoneDigits($p); });
                            $validPhones = array_values($validPhones);
                            
                            $finalPhone = $validPhones[0] ?? null;
                            $finalPhone2 = $validPhones[1] ?? null;
                            
                            if ($finalPhone !== null && $finalPhone2 !== null && $finalPhone === $finalPhone2) {
                                $finalPhone2 = null;
                            }
                            
                            $rawEmail = $rowAssoc['email'] ?? '';
                            $cleanEmail = $this->cleanEmail($rawEmail); 
                            $emailLooksOk = $cleanEmail !== '' && strpos($cleanEmail, '@') !== false;
                            $emailToStore = $emailLooksOk ? strtolower($cleanEmail) : '';
                            
                            $destination_name = trim($rowAssoc['destination_name'] ?? '');
                            $address = trim($rowAssoc['address'] ?? '');

                            $destinationIsReal = $this->isRealText($destination_name);
                            $addressIsReal = $this->isRealText($address);
                            $hasAnyImportant = $destinationIsReal || $addressIsReal || $emailLooksOk || count($validPhones) > 0;
                            
                            if (! $hasAnyImportant) {
                                $rowAssoc['rejection_reason'] = 'No usable data in destination/address/email/phone';
                                $invalidData[] = $rowAssoc;
                                Log::info('Row rejected (no usable important fields): ', $rowAssoc);
                                continue;
                            }
                            
                            $rowAssoc['phone'] = $finalPhone;
                            $rowAssoc['phone2'] = $finalPhone2;
                            $rowAssoc['email'] = $emailToStore;
                            $rowAssoc['destination_name'] = $destination_name ?: null;
                            $rowAssoc['address'] = $address ?: null;

                            $rejectionReason = [];

                            $validationErrors = $this->validateDataLengths($rowAssoc);
                            if (!empty($validationErrors)) {
                                $rejectionReason = array_merge($rejectionReason, $validationErrors);
                            }
                            
                            $isDuplicateInFile = false;
                            foreach ($fileSeenRows as $seen) {
                                if ($this->rowsAreDuplicateByImportantFields($seen, [
                                    'destination_name' => $rowAssoc['destination_name'],
                                    'email' => $rowAssoc['email'],
                                    'phones' => array_values(array_filter([$rowAssoc['phone'], $rowAssoc['phone2']])),
                                ])) {
                                    $isDuplicateInFile = true;
                                    break;
                                }
                            }
                            
                            if ($isDuplicateInFile) {
                                $rejectionReason[] = 'Duplicate within the same uploaded file';
                            } else {
                                $fileSeenRows[] = [
                                    'destination_name' => $rowAssoc['destination_name'],
                                    'email' => $rowAssoc['email'],
                                    'phones' => array_values(array_filter([$rowAssoc['phone'], $rowAssoc['phone2']])),
                                ];
                            }
                            
                            if (!empty($rejectionReason)) {
                                $rowAssoc['rejection_reason'] = implode(', ', $rejectionReason);
                                $invalidData[] = $rowAssoc;
                                Log::info('Invalid Data Row (pre-db-check): ', $rowAssoc);
                                continue;
                            }

                            // إضافة البيانات للمعالجة المجمعة
                            $batchData[] = $rowAssoc;
                        }
                        
                        // معالجة الدفعة الحالية
                        if (!empty($batchData)) {
                            $this->processBatchData($batchData, $cat, $subcat, $request, $invalidData);
                            $batchData = []; // إفراغ المصفوفة للدفعة التالية
                        }
                        
                        $processedRows += count($currentChunk);
                        
                        // تنظيف الذاكرة كل 1000 صف
                        if ($processedRows % 1000 == 0) {
                            if (function_exists('gc_collect_cycles')) {
                                gc_collect_cycles();
                            }
                        }
                    } // end chunk processing
                } // end sheets loop for file
            } // end files loop

            $showInvalidHeadersButton = false;
            $showDownloadButton = false;

            if (!empty($invalidHeaders)) {
                session()->put('invalidHeaders', $invalidHeaders);
                $showInvalidHeadersButton = true;
            }
            if (!empty($invalidData)) {
                session()->put('invalidData', $invalidData);
                Log::info('Stored Invalid Data in Session: ', $invalidData);
                $showDownloadButton = true;
            }

            if ($showInvalidHeadersButton || $showDownloadButton) {
                if ($showInvalidHeadersButton) {
                    toastr()->warning('Some files have invalid headers. You can download the invalid headers report.');
                }
                if ($showDownloadButton) {
                    toastr()->error('Import completed with errors: some rows were invalid or duplicates. You can download the invalid data file.');
                }
                return redirect()->route('home.index')->with([
                    'showInvalidHeadersButton' => $showInvalidHeadersButton,
                    'showDownloadButton' => $showDownloadButton,
                ]);
            }

            toastr()->success('All files have been imported successfully.');
            return redirect()->route('home.index');
        } catch (\Exception $e) {
            Log::error('Error importing data: ' . $e->getMessage());
            toastr()->error('An error occurred during the import process. Please check your file format and try again.');
            return redirect()->back();
        }
    }

    /**
     * معالجة البيانات في دفعات لتحسين الأداء
     */
    private function processBatchData(array &$batchData, $cat, $subcat, $request, array &$invalidData)
    {
        if (empty($batchData)) return;

        // تجميع البيانات للتحقق من التكرار في قاعدة البيانات
        $emailsToCheck = [];
        $phonesToCheck = [];
        $destinationsToCheck = [];

        foreach ($batchData as $index => $rowAssoc) {
            if (!empty($rowAssoc['email'])) {
                $emailsToCheck[] = $rowAssoc['email'];
            }
            
            $phones = array_filter([$rowAssoc['phone'], $rowAssoc['phone2']]);
            if (!empty($phones)) {
                $phonesToCheck = array_merge($phonesToCheck, $phones);
            }
            
            if (!empty($rowAssoc['destination_name'])) {
                $destinationsToCheck[] = $rowAssoc['destination_name'];
            }
        }

        // استعلام مجمع للتحقق من البيانات الموجودة
        $existingRecords = [];
        if (!empty($emailsToCheck) || !empty($phonesToCheck) || !empty($destinationsToCheck)) {
            $query = DataExcel::query();
            
            if (!empty($emailsToCheck)) {
                $query->orWhereIn('email', array_unique($emailsToCheck));
            }
            
            if (!empty($phonesToCheck)) {
                $uniquePhones = array_unique($phonesToCheck);
                $query->orWhereIn('phone', $uniquePhones)
                      ->orWhereIn('phone2', $uniquePhones);
            }
            
            if (!empty($destinationsToCheck)) {
                $query->orWhereIn('destination_name', array_unique($destinationsToCheck));
            }

            $existingRecords = $query->get(['destination_name', 'email', 'phone', 'phone2'])
                                   ->keyBy(function($item) {
                                       return md5($item->destination_name . $item->email . $item->phone . $item->phone2);
                                   });
        }

        // معالجة كل صف في الدفعة
        $validRecordsToInsert = [];
        
        foreach ($batchData as $rowAssoc) {
            try {
                // التحقق من وجود السجل
                $exists = $this->checkRecordExistsInBatch($rowAssoc, $existingRecords);

                if ($exists) {
                    $rowAssoc['rejection_reason'] = 'Already exists';
                    $invalidData[] = $rowAssoc;
                    Log::info('Duplicate Entry Found in Database: ', $rowAssoc);
                    continue;
                }

                // إضافة السجل للإدراج
                $validRecordsToInsert[] = [
                    'destination_name' => $rowAssoc['destination_name'] ?? null,
                    'address' => $rowAssoc['address'] ?? null,
                    'phone' => $rowAssoc['phone'] ?? null,
                    'phone2' => $rowAssoc['phone2'] ?? null,
                    'email' => !empty($rowAssoc['email']) ? $rowAssoc['email'] : null,
                    'country' => $rowAssoc['country'] ?? 'Egypt',
                    'website' => $rowAssoc['website'] ?? null,
                    'google_map' => $rowAssoc['google_map'] ?? null,
                    'responsible_name' => $rowAssoc['responsible_name'] ?? null,
                    'category_id' => $cat->id,
                    'subcategory_id' => $subcat->id,
                    'created_at' => now(),
                    'updated_at' => now(),
                ];

            } catch (\Exception $e) {
                Log::error('Error processing row: ' . json_encode($rowAssoc) . ' - Error: ' . $e->getMessage());
                $rowAssoc['rejection_reason'] = 'System error occurred';
                $invalidData[] = $rowAssoc;
                continue;
            }
        }

        // إدراج مجمع للسجلات الصحيحة
        if (!empty($validRecordsToInsert)) {
            try {
                // استخدام chunks للإدراج في دفعات أصغر
                $insertChunks = array_chunk($validRecordsToInsert, 100);
                
                foreach ($insertChunks as $chunk) {
                    DB::table('data_excels')->insert($chunk);
                }
                
            } catch (\Exception $e) {
                Log::error('Batch insert failed: ' . $e->getMessage());
                
                // في حالة فشل الإدراج المجمع، نعود للإدراج الفردي
                foreach ($validRecordsToInsert as $record) {
                    try {
                        DataExcel::create($record);
                    } catch (\Exception $individualError) {
                        Log::error('Individual insert failed: ' . $individualError->getMessage());
                    }
                }
            }
        }
    }

    /**
     * التحقق من وجود السجل في النتائج المجمعة
     */
    private function checkRecordExistsInBatch($rowAssoc, $existingRecords)
    {
        foreach ($existingRecords as $existing) {
            $phonesToCheck = array_values(array_filter([$rowAssoc['phone'], $rowAssoc['phone2']]));
            $existingPhones = array_values(array_filter([$existing->phone, $existing->phone2]));
            
            // نفس منطق التحقق الموجود في الكود الأصلي
            if (!empty($rowAssoc['destination_name']) && $rowAssoc['email'] !== '' && count($phonesToCheck) > 0) {
                if ($existing->destination_name === $rowAssoc['destination_name'] && 
                    $existing->email === $rowAssoc['email'] &&
                    count(array_intersect($phonesToCheck, $existingPhones)) > 0) {
                    return true;
                }
            }
            
            elseif (!empty($rowAssoc['destination_name']) && count($phonesToCheck) > 0) {
                if ($existing->destination_name === $rowAssoc['destination_name'] &&
                    count(array_intersect($phonesToCheck, $existingPhones)) > 0) {
                    return true;
                }
            }
            
            elseif ($rowAssoc['email'] !== '' && count($phonesToCheck) > 0) {
                if ($existing->email === $rowAssoc['email'] &&
                    count(array_intersect($phonesToCheck, $existingPhones)) > 0) {
                    return true;
                }
            }
            
            elseif ($rowAssoc['email'] !== '' && count($phonesToCheck) === 0 && empty($rowAssoc['destination_name'])) {
                if ($existing->email === $rowAssoc['email'] &&
                    empty($existing->phone) && empty($existing->phone2)) {
                    return true;
                }
            }
        }
        
        return false;
    }

    private function rowsAreDuplicateByImportantFields(array $a, array $b): bool
    {
        $get = function($v) {
            if ($v === null) return '';
            return trim(mb_strtolower((string)$v, 'UTF-8'));
        };

        $a_dest = $get($a['destination_name'] ?? '');
        $a_email = $get($a['email'] ?? '');
        $a_phones = $a['phones'] ?? [];
        $a_phones = array_map(function($p){ return preg_replace('/\D+/', '', (string)$p); }, $a_phones);
        $a_phones = array_filter($a_phones);

        $b_dest = $get($b['destination_name'] ?? '');
        $b_email = $get($b['email'] ?? '');
        $b_phones = $b['phones'] ?? [];
        $b_phones = array_map(function($p){ return preg_replace('/\D+/', '', (string)$p); }, $b_phones);
        $b_phones = array_filter($b_phones);
        
        $phonesIntersect = function($x, $y) {
            if (empty($x) || empty($y)) return false;
            return count(array_intersect($x, $y)) > 0;
        };
        
        if ($a_dest !== '' && $a_email !== '' && !empty($a_phones) && $b_dest !== '' && $b_email !== '' && !empty($b_phones)) {
            return ($a_dest === $b_dest) && ($a_email === $b_email) && $phonesIntersect($a_phones, $b_phones);
        }
        if ($a_dest !== '' && !empty($a_phones) && $b_dest !== '' && !empty($b_phones)) {
            if ($a_dest === $b_dest && $phonesIntersect($a_phones, $b_phones)) return true;
        }
        if ($a_email !== '' && !empty($a_phones) && $b_email !== '' && !empty($b_phones)) {
            if ($a_email === $b_email && $phonesIntersect($a_phones, $b_phones)) return true;
        }
        if ($a_dest !== '' && $a_email !== '' && $b_dest !== '' && $b_email !== '') {
            if ($a_dest === $b_dest && $a_email === $b_email) return true;
        }
        if ($a_email !== '' && $b_email !== '' && empty($a_phones) && empty($b_phones) && $a_email === $b_email) {
            return true;
        }
        return false;
    }

    /**
     * Extract potential phone numbers from a cell string.
     * Returns array of digit-only numbers (no formatting).
     * It finds sequences of digits of length >=4 (we'll later validate 7-15).
     */
    private function extractPhoneNumbersFromCell($cell): array
    {
        if (!$cell) return [];
        $cell = (string)$cell;
        // replace common separators with comma
        $cell = str_replace([';', '/', '|', '\\'], ',', $cell);
        // split by comma or whitespace
        $parts = preg_split('/[,\s]+/', $cell);
        $found = [];
        foreach ($parts as $p) {
            $digits = preg_replace('/\D+/', '', $p);
            if ($digits !== '') $found[] = $digits;
        }
        // unique and return
        $found = array_values(array_unique($found));
        return $found;
    }

    /**
     * isValidPhoneDigits: digits-only string, length between 7 and 15 -> valid
     */
    private function isValidPhoneDigits($digits): bool
    {
        if (!$digits) return false;
        $digits = preg_replace('/\D+/', '', (string)$digits);
        $len = strlen($digits);
        return ($len >= 7 && $len <= 15);
    }

    /**
     * Heuristic to check if a textual field (destination/address) looks "real"
     * (not empty, not 1 char, not useless)
     */
    private function isRealText($s): bool
    {
        if (!$s) return false;
        $s = trim((string)$s);
        if (mb_strlen($s) < 3) return false;
        // if it's only punctuation or only numbers shorter than 4 -> reject
        $letters = preg_replace('/[^[:alnum:]\p{L}]/u', '', $s);
        if ($letters === '') return false;
        return true;
    }

    /**
     * Clean email: permissive cleaning — used as preprocessing.
     */
    private function cleanEmail($email)
    {
        if (!$email) return '';
        $email = (string)$email;
        // remove BOM, NBSP, zero-width etc
        $email = str_replace(["\xEF\xBB\xBF", "\xC2\xA0", "\xE2\x80\x8B", "\xE2\x80\x8C", "\xE2\x80\x8D"], '', $email);
        $email = trim($email, " \t\n\r\0\x0B\"'");
        return $email;
    }

    /**
     * Clean phone number - permissive trimming only (we use extractPhoneNumbersFromCell + isValidPhoneDigits for validation)
     */
    private function cleanPhoneNumber($number)
    {
        if ($number === null || $number === '') return null;
        $number = (string)$number;
        $number = preg_replace('/[\x00-\x1F\x7F-\x9F]+/u', '', $number);
        $number = trim($number);
        if ($number === '') return null;
        return $number;
    }

    /**
     * Sanitize header values - remove unwanted characters and trim
     */
    private function sanitizeHeader($header)
    {
        if ($header === null || $header === '') {
            return '';
        }
        $header = trim((string) $header);
        $header = preg_replace('/[\x00-\x1F\x7F-\x9F\x{200B}\x{200C}\x{200D}\x{FEFF}\x{00A0}]+/u', '', $header);
        $header = preg_replace('/\s+/u', ' ', $header);
        return trim($header);
    }

    /**
     * Normalize header for comparison - make it lowercase and remove all spaces/special chars
     */
    private function normalizeHeader($header)
    {
        if ($header === null || $header === '') {
            return '';
        }
        $header = $this->sanitizeHeader($header);
        $header = mb_strtolower($header, 'UTF-8');
        $header = preg_replace('/[\s_\-\.\x{200B}\x{200C}\x{200D}\x{FEFF}\x{00A0}]+/u', '', $header);
        $header = preg_replace('/[^\p{L}\p{N}]/u', '', $header);
        return $header;
    }

    /**
     * Sanitize cell values
     */
    private function sanitizeValue($value)
    {
        if ($value === null || $value === '') {
            return '';
        }
        $value = trim((string) $value);
        $value = preg_replace('/[\x00-\x1F\x7F-\x9F\x{200B}\x{200C}\x{200D}\x{FEFF}]+/u', '', $value);
        $value = preg_replace('/\s+/u', ' ', $value);
        return trim($value);
    }

    /**
     * Validate data lengths against database column limits
     * (left permissive — you can set real numeric limits if needed)
     */
    private function validateDataLengths($data)
    {
        $errors = [];
        // no strict length enforcement here; modify if you have schema limits
        return $errors;
    }

    public function downloadInvalidDataReport()
    {
        $invalidData = session()->get('invalidData', []);
        if (empty($invalidData)) {
            toastr()->error('لا توجد بيانات مرفوضة متاحة للتنزيل.');
            return redirect()->route('home.index');
        }
        // include original phone/email raw fields so report shows original values and reason
        $allHeaders = [
            'destination_name', 'email', 'original_email_raw', 'address', 'phone', 'phone2', 'original_phone_raw', 'original_phone2_raw',
            'country', 'website', 'google_map', 'responsible_name', 'category_name', 'subcategory_name', 'rejection_reason'
        ];
        $response = new StreamedResponse(function () use ($invalidData, $allHeaders) {
            $output = fopen('php://output', 'w');
            fputcsv($output, $allHeaders);
            foreach ($invalidData as $row) {
                $line = [];
                foreach ($allHeaders as $header) {
                    $line[] = $row[$header] ?? '';
                }
                fputcsv($output, $line);
            }
            fclose($output);
        });
        $response->headers->set('Content-Type', 'text/csv; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment; filename="invalid_data_report.csv"');
        $response->headers->set('Cache-Control', 'no-cache');
        session()->forget('invalidData');
        return $response;
    }

    public function downloadInvalidHeadersReport()
    {
        $invalidHeaders = session()->get('invalidHeaders', []);
        if (empty($invalidHeaders)) {
            toastr()->error('No invalid headers data to download.');
            return redirect()->route('home.index');
        }
        $headers = [ 'file_name' => 'File Name', 'missing_headers' => 'Missing Headers', 'found_headers' => 'Found Headers', ];
        $response = new StreamedResponse(function () use ($invalidHeaders, $headers) {
            $output = fopen('php://output', 'w');
            fputcsv($output, array_values($headers));
            foreach ($invalidHeaders as $row) {
                $data = [
                    $row['file_name'] ?? '',
                    $row['missing_headers'] ?? '',
                    $row['found_headers'] ?? '',
                ];
                fputcsv($output, $data);
            }
            fclose($output);
        });
        $response->headers->set('Content-Type', 'text/csv; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment; filename="invalid_headers_report.csv"');
        $response->headers->set('Cache-Control', 'no-cache');
        session()->forget('invalidHeaders');
        return $response;
    }
}